﻿Imports System.Data.SqlClient
Imports DMC.Land.User
Imports System.Xml

Public Class ctrInSo
    Dim app As New Object
    Private strNamIn As Int16 = 0
    Private strConnection As String = ""
    Public User As New clsUser
    Private strUsername As String = ""
    Private strDVHC() As Int32
    Private strMaQuyen As String = ""
    Dim conn = New SqlConnection(strConnection)
    Public dt As New DataTable()
    Private strMaChu As String = ""
    Private straaa As String = ""
    Public Property Get_straa() As String
        Get
            Return straaa
        End Get
        Set(ByVal value As String)
            straaa = value
        End Set
    End Property

    Private dt1 As New DataTable()
    Public Property DataTable1() As DataTable
        Get
            Return dt1
        End Get
        Set(ByVal value As DataTable)
            dt1 = value
        End Set
    End Property
    Public Property Get_MaChu() As String
        Get
            Return strMaChu
        End Get
        Set(ByVal value As String)
            strMaChu = value
        End Set
    End Property

    Public Property MaQuyen() As String
        Get
            Return strMaQuyen
        End Get
        Set(ByVal value As String)
            strMaQuyen = value
        End Set
    End Property
    Public Property DVHC() As Int32()
        Get
            Return strDVHC
        End Get
        Set(ByVal value As Int32())
            strDVHC = value
        End Set
    End Property
    Public Property UserName() As String
        Get
            Return strUsername
        End Get
        Set(ByVal value As String)
            strUsername = value
        End Set
    End Property
    Public Property Connection() As String
        Get
            Return strConnection
        End Get
        Set(ByVal value As String)
            strConnection = value
        End Set
    End Property

    Public Sub ctrInSo_Load() '(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            'connect.Open()
            LoadNam()
            cboNam.Text = Year(Now)
            conn.ConnectionString = strConnection
            Try
                conn.Open()
            Catch ex As Exception
                MessageBox.Show("Lỗi kết nối" & ex.Message)
            End Try
            CtrChonDonViHanhChinh1.Get_arrMaDVHC = strDVHC  'frmDangNhap.UcKetNoi1.Get_MaDVHC ' User.Get_MaDVHC
            CtrChonDonViHanhChinh1.Get_Maquyen = strMaQuyen 'frmDangNhap.UcKetNoi1.Get_MaQuyen ' User.Get_MaQuyen
            CtrChonDonViHanhChinh1.Get_sqlConnect = conn
            CtrChonDonViHanhChinh1.HienThiThongTinLenTreeview()
        Catch ex As Exception
            MsgBox("Connection failen !")

            Me.Dispose()
        End Try
    End Sub

    Public Sub LoadNam()
        cboNam.Items.Clear()
        For i As Int16 = Now.Year() To Now.Year() - 10 Step -1
            cboNam.Items.Add(i)
        Next
    End Sub

    Private Sub btnSoCapGCN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoCapGCN.Click
        Dim cls As New clsInSo
        cls.Connection = strConnection
        If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
            MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ cấp GCN", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If

        Dim MytableSoCapGCN As New DataTable
        Dim parasSoCapGCN() As String = {"@MaDVHC", "@Nam"}
        Dim ValuesSSoCGCN(1) As String

        ValuesSSoCGCN(0) = CtrChonDonViHanhChinh1.Current_DVHC
        ValuesSSoCGCN(1) = GetNam()




        MytableSoCapGCN = cls.GetData_SP("SP_SoCapGCN", ValuesSSoCGCN, parasSoCapGCN)

        If MytableSoCapGCN.Rows.Count <> 0 Then

            MytableSoCapGCN.Columns.Add("Tinh")
            MytableSoCapGCN.Columns.Add("Huyen")
            MytableSoCapGCN.Columns.Add("Xa")
            MytableSoCapGCN.Columns.Add("MaTinh")
            MytableSoCapGCN.Columns.Add("MaHuyen")
            MytableSoCapGCN.Columns.Add("MaXa")
            MytableSoCapGCN.Rows(0).Item("Tinh") = DonViHanhChinh(0)
            MytableSoCapGCN.Rows(0).Item("Huyen") = DonViHanhChinh(1)
            MytableSoCapGCN.Rows(0).Item("Xa") = DonViHanhChinh(2)



            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim dt As New DataTable
            Dim s As String = "SELECT MaTinh,Mahuyen,MaXa FROM tblTuDienDonViHanhChinh where MaDonViHanhChinh=" & CtrChonDonViHanhChinh1.Current_DVHC.ToString
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)
            MytableSoCapGCN.Rows(0).Item("MaTinh") = dt.Rows(0)(0)
            MytableSoCapGCN.Rows(0).Item("MaHuyen") = dt.Rows(0)(1)
            MytableSoCapGCN.Rows(0).Item("MaXa") = dt.Rows(0)(2)

        End If

        Dim frm As New frmSoCapGNC
        frm.DB = MytableSoCapGCN
        frm.LoadReport()
        frm.Show()
        'Try

        '    app = CreateObject("Excel.Application")
        'Catch ex As Exception
        '    'If app Is Nothing Then
        '    MsgBox("Hãy cài đặt Excell", MsgBoxStyle.Critical, "DMCLand")
        '    'End If
        '    Exit Sub
        'End Try

        'Try
        '    If MsgBox("Bạn có muốn lưu file?", MsgBoxStyle.YesNo, "Sổ cấp GCN") = MsgBoxResult.Yes Then

        '        SfDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|Excel 97-2003 Workbook (*.xls)|*.xls"
        '        SfDialog.FileName = "SoCapGCN" & cls.DuongDanFile()

        '        cls.DonViHanhChinh = CtrChonDonViHanhChinh1.Current_DVHC
        '        SfDialog.ShowDialog()
        '        If SfDialog.FileName <> "" Then
        '            '@Flag=1 
        '            'Dim s As String = "SELECT MaHoSoCapGCN,SoPhatHanhGCN,NgayKyGCN,NgayGiaoGCN,NguoiKyGCN,GhiChuGCN FROM tblHoSoCapGCN WHERE HoanTatCapGCN =7"
        '            'Dim s As String = "SELECT MaHoSoCapGCN,SoPhatHanhGCN,NgayKyGCN,NgayGiaoGCN,NguoiNhanGCN,GhiChuGCN FROM tblHoSoCapGCN WHERE HoanTatCapGCN =1"
        '            cls.Config(app, "a3", "dung")
        '            cls.TrangMoDauSoCapGCN(app, DonViHanhChinh)
        '            cls.ExpExSoCapGCN(app, GetNam())
        '            cls.LuuFile(app, SfDialog.FileName)
        '        End If
        '    End If
        '    app = Nothing
        'Catch ex As Exception

        '    app = Nothing
        '    MsgBox("    Có lỗi xảy ra!" & vbCrLf & "   Hãy thử lại", MsgBoxStyle.Critical, "DMCLand")
        'End Try
        'cls.ds.Dispose()
        'cls.da.Dispose()

        'btnSoCapGCN.Cursor = Cursors.Default

    End Sub

    Private Sub btnSoDiaChinh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoDiaChinh.Click
        Dim cls As New clsInSo
        cls.Connection = strConnection

        If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
            MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ Địa Chính", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        Dim MyTableSoDiaChinh As New DataTable
        Dim parasSoDiaChinh() As String = {"@MaDVHC", "@Nam", "@MaChu"}
        Dim ValuesSoDiaChinh(2) As String
        If strMaChu = "" Then
            ValuesSoDiaChinh(0) = CtrChonDonViHanhChinh1.Current_DVHC
            ValuesSoDiaChinh(1) = GetNam()
            ValuesSoDiaChinh(2) = ""
        ElseIf strMaChu <> "" Then
            ValuesSoDiaChinh(0) = CtrChonDonViHanhChinh1.Current_DVHC
            ValuesSoDiaChinh(1) = GetNam()
            ValuesSoDiaChinh(2) = strMaChu
        End If
        If (RadInSoDiaChinhByCSD.Checked = True) Then           
            MyTableSoDiaChinh = dt1
            'MessageBox.Show(Get_straa)
        Else
            MyTableSoDiaChinh = cls.GetData_SP("SP_SoDiaChinh", ValuesSoDiaChinh, parasSoDiaChinh)
        End If
        
        If MyTableSoDiaChinh.Rows.Count <> 0 Then

            MyTableSoDiaChinh.Columns.Add("Tinh")
            MyTableSoDiaChinh.Columns.Add("Huyen")
            MyTableSoDiaChinh.Columns.Add("Xa")
            MyTableSoDiaChinh.Columns.Add("MaTinh")
            MyTableSoDiaChinh.Columns.Add("MaHuyen")
            MyTableSoDiaChinh.Columns.Add("MaXa")
            MyTableSoDiaChinh.Rows(0).Item("Tinh") = DonViHanhChinh(0)
            MyTableSoDiaChinh.Rows(0).Item("Huyen") = DonViHanhChinh(1)
            MyTableSoDiaChinh.Rows(0).Item("Xa") = DonViHanhChinh(2)



            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim dt As New DataTable
            Dim s As String = "SELECT MaTinh,Mahuyen,MaXa FROM tblTuDienDonViHanhChinh where MaDonViHanhChinh=" & CtrChonDonViHanhChinh1.Current_DVHC.ToString
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)
            MyTableSoDiaChinh.Rows(0).Item("MaTinh") = dt.Rows(0)(0)
            MyTableSoDiaChinh.Rows(0).Item("MaHuyen") = dt.Rows(0)(1)
            MyTableSoDiaChinh.Rows(0).Item("MaXa") = dt.Rows(0)(2)

        End If

        Dim MyTableSoDiaChinhThuaDat As New DataTable
        Dim cnt As New SqlCommand
        Dim dc As New SqlDataAdapter
        Dim f As String = "SELECT * From SoDiaChinhThuaDat "
        cnt.Connection = conn
        cnt.CommandType = CommandType.Text
        cnt.CommandText = f
        dc.SelectCommand = cnt
        MyTableSoDiaChinhThuaDat.Clear()
        dc.Fill(MyTableSoDiaChinhThuaDat)

        Dim MyTableSoDiaChinhThayDoi As New DataTable
        Dim ctt As New SqlCommand
        Dim dd As New SqlDataAdapter
        Dim g As String = "SELECT * From SoDiaChinhNoiDungThayDoi"
        ctt.Connection = conn
        ctt.CommandType = CommandType.Text
        ctt.CommandText = g
        dd.SelectCommand = ctt
        MyTableSoDiaChinhThayDoi.Clear()
        dd.Fill(MyTableSoDiaChinhThayDoi)

        Dim frm As New frmSoDiaChinh
        frm.DB = MyTableSoDiaChinh
        frm.TB = MyTableSoDiaChinhThuaDat
        frm.TT = MyTableSoDiaChinhThayDoi
        frm.LoadReport()
        frm.Show()
        strMaChu = ""

        'btnSoCapGCN.Cursor = Cursors.WaitCursor
        'Dim cls As New clsInSo
        'cls.Connection = strConnection
        'If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
        '    MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ cấp GCN", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        '    Exit Sub
        'End If
        'Try

        '    app = CreateObject("Excel.Application")
        'Catch ex As Exception
        '    'If app Is Nothing Then
        '    MsgBox("Hãy cài đặt Excell", MsgBoxStyle.Critical, "DMCLand")
        '    'End If
        '    Exit Sub
        'End Try

        '' Try
        'If MsgBox("Bạn có muốn lưu file?", MsgBoxStyle.YesNo, "Sổ địa chính") = MsgBoxResult.Yes Then

        '    SfDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|Excel 97-2003 Workbook (*.xls)|*.xls"
        '    SfDialog.FileName = "SoDiaChinh" & cls.DuongDanFile()
        '    SfDialog.ShowDialog()
        '    If SfDialog.FileName <> "" Then

        '        Dim dr As DataRow
        '        Dim iSoDongCuaTrang As Integer = 67
        '        Dim i As Integer

        '        cls.Config(app, "a3", "dung")
        '        cls.TrangMoDauSoDiaChinh(app, DonViHanhChinh)
        '        'lay so luong ho so cua so dia chinh

        '        Dim MyTableSoDiaChinh As New DataTable
        '        If RadInSoDiaChinhAll.Checked Then
        '            Dim parasSoDiaChinh() As String = {"@Flag", "@MaDVHC"}
        '            Dim ValuesSoDiaChinh() As String = {0, CtrChonDonViHanhChinh1.Current_DVHC}
        '            MyTableSoDiaChinh = cls.GetData_SP("spInSoDiaChinhGetCSD", ValuesSoDiaChinh, parasSoDiaChinh)
        '        Else
        '            If grdChuSuDung.Rows.Count > 0 Then
        '                Dim strMaCSD As String = ""
        '                For k As Integer = 0 To grdChuSuDung.Rows.Count - 1
        '                    strMaCSD = strMaCSD & grdChuSuDung.Rows(k).Cells("MaChu").Value & ","
        '                Next
        '                Dim parasSoDiaChinh() As String = {"@Flag", "@MaDVHC", "@MaChuSuDung"}
        '                Dim ValuesSoDiaChinh() As String = {2, CtrChonDonViHanhChinh1.Current_DVHC, strMaCSD}
        '                MyTableSoDiaChinh = cls.GetData_SP("spInSoDiaChinhGetCSD", ValuesSoDiaChinh, parasSoDiaChinh)
        '            End If
        '        End If

        '        For i = 0 To MyTableSoDiaChinh.Rows.Count - 1
        '            dr = MyTableSoDiaChinh.Rows(i)
        '            cls.KhungSoDiaChinh(app, i, iSoDongCuaTrang)
        '            cls.ExportSoDiaChinh(app, MyTableSoDiaChinh.Rows(i).Item("maChu"), i, iSoDongCuaTrang, GetNam(), CtrChonDonViHanhChinh1.Current_DVHC)
        '            'dr.Table.Dispose()   '.Delete()
        '        Next


        '        ' '''ExportMucLucSoDiaChinh(app, i, iSoDongCuaTrang)
        '        cls.LuuFile(app, SfDialog.FileName)
        '    End If
        'End If
        'app = Nothing
        '' Catch ex As Exception
        ''app = Nothing
        ''MsgBox("    Có lỗi xảy ra!" & vbCrLf & "   Hãy thử lại", MsgBoxStyle.Critical, "DMCLand")
        ''End Try
        'cls.ds.Dispose()
        'cls.da.Dispose()

        'btnSoCapGCN.Cursor = Cursors.Default
    End Sub

    Private Sub btnSoMucKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoMucKe.Click
        Dim cls As New clsInSo
        cls.Connection = strConnection

        If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
            MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ Mục Kê", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        Dim MyTableSoMucKe As New DataTable
        Dim parasSoMucKe() As String = {"@MaDVHC", "@NamIn"}
        Dim ValuesSoMucKe() As String = {CtrChonDonViHanhChinh1.Current_DVHC, GetNam()}

        MyTableSoMucKe = cls.GetData_SP("SP_SoMucKe", ValuesSoMucKe, parasSoMucKe)


        Dim tobando1 As String
        Dim mahoso1 As String
        Dim tobando2 As String
        Dim mahoso2 As String
        Dim mathua1 As String
        Dim mathua2 As String
        Dim sothua As String
        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1
            tobando1 = MyTableSoMucKe.Rows(i)(1).ToString
            mahoso1 = MyTableSoMucKe.Rows(i)(3).ToString
            For j As Integer = i + 1 To MyTableSoMucKe.Rows.Count - 1
                tobando2 = MyTableSoMucKe.Rows(j)(1).ToString
                mahoso2 = MyTableSoMucKe.Rows(j)(3).ToString
                If tobando1 = tobando2 And mahoso1 = mahoso2 And mahoso1 <> "" And mahoso2 <> "" Then
                    sothua = MyTableSoMucKe.Rows(j)(2).ToString
                    MyTableSoMucKe.Rows(j)(2) = Replace(MyTableSoMucKe.Rows(j)(2), MyTableSoMucKe.Rows(j)(2), " ")
                End If
            Next

        Next

        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1
            mathua1 = MyTableSoMucKe.Rows(i)(2).ToString
            For j As Integer = i + 1 To MyTableSoMucKe.Rows.Count - 1
                mathua2 = MyTableSoMucKe.Rows(j)(2).ToString
                If mathua1 = mathua2 And mathua1 <> "" And mathua2 <> "" Then
                    MyTableSoMucKe.Rows(j)(2) = Replace(MyTableSoMucKe.Rows(j)(2), MyTableSoMucKe.Rows(j)(2), "-")
                End If
            Next
        Next


        If MyTableSoMucKe.Rows.Count <> 0 Then

            MyTableSoMucKe.Columns.Add("Tinh")
            MyTableSoMucKe.Columns.Add("Huyen")
            MyTableSoMucKe.Columns.Add("Xa")
            MyTableSoMucKe.Columns.Add("MaTinh")
            MyTableSoMucKe.Columns.Add("MaHuyen")
            MyTableSoMucKe.Columns.Add("MaXa")
            MyTableSoMucKe.Columns.Add("CacToBanDo")
            MyTableSoMucKe.Rows(0).Item("Tinh") = DonViHanhChinh(0)
            MyTableSoMucKe.Rows(0).Item("Huyen") = DonViHanhChinh(1)
            MyTableSoMucKe.Rows(0).Item("Xa") = DonViHanhChinh(2)



            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim dt As New DataTable
            Dim s As String = "SELECT MaTinh,Mahuyen,MaXa FROM tblTuDienDonViHanhChinh where MaDonViHanhChinh=" & CtrChonDonViHanhChinh1.Current_DVHC.ToString
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)
            MyTableSoMucKe.Rows(0).Item("MaTinh") = dt.Rows(0)(0)
            MyTableSoMucKe.Rows(0).Item("MaHuyen") = dt.Rows(0)(1)
            MyTableSoMucKe.Rows(0).Item("MaXa") = dt.Rows(0)(2)


            Dim cnd As New SqlCommand
            Dim db As New SqlDataAdapter
            Dim ds As New DataTable
            Dim k As String = "SELECT DISTINCT ToBanDo From SoMucKe "
            cnd.Connection = conn
            cnd.CommandType = CommandType.Text
            cnd.CommandText = k
            db.SelectCommand = cnd
            ds.Clear()
            db.Fill(ds)
            Dim str As String = ""
            For i As Integer = 0 To ds.Rows.Count - 1
                str += ds.Rows(i)(0).ToString.Trim & ",".Trim
            Next
            MyTableSoMucKe.Rows(0).Item("CacToBanDo") = str
        End If



        Dim bando1 As String = ""
        Dim bando2 As String = ""
        Dim noidung1 As String = ""
        Dim noidung2 As String = ""


        Dim MyTableSoMucKeCon As New DataTable
        Dim cnt As New SqlCommand
        Dim dc As New SqlDataAdapter
        Dim f As String = "SELECT * From SoMucKeCon "
        cnt.Connection = conn
        cnt.CommandType = CommandType.Text
        cnt.CommandText = f
        dc.SelectCommand = cnt
        MyTableSoMucKeCon.Clear()
        dc.Fill(MyTableSoMucKeCon)


        Dim tobandocon1 As String
        Dim mahosocon1 As String
        Dim tobandocon2 As String
        Dim mahosocon2 As String
        Dim mathuacon1 As String
        Dim mathuacon2 As String
        For i As Integer = 0 To MyTableSoMucKeCon.Rows.Count - 1
            tobandocon1 = MyTableSoMucKeCon.Rows(i)(1).ToString
            mahosocon1 = MyTableSoMucKeCon.Rows(i)(3).ToString
            For j As Integer = i + 1 To MyTableSoMucKeCon.Rows.Count - 1
                tobandocon2 = MyTableSoMucKeCon.Rows(j)(1).ToString

                mahosocon2 = MyTableSoMucKeCon.Rows(j)(3).ToString
                Dim SoTTThua As String
                SoTTThua = MyTableSoMucKeCon.Rows(j)(2).ToString
                If tobandocon1 = tobandocon2 And mahosocon1 = mahosocon2 And mahosocon1 <> "" And mahosocon2 <> "" Then
                    MyTableSoMucKeCon.Rows(j)(2) = Replace(MyTableSoMucKeCon.Rows(j)(2), MyTableSoMucKeCon.Rows(j)(2), " ")
                End If
            Next
        Next

        For i As Integer = 0 To MyTableSoMucKeCon.Rows.Count - 1
            mathuacon1 = MyTableSoMucKeCon.Rows(i)(2)
            For j As Integer = i + 1 To MyTableSoMucKeCon.Rows.Count - 1
                mathuacon2 = MyTableSoMucKeCon.Rows(j)(2)
                If mathuacon1 = mathuacon2 And mathuacon1 <> "" And mathuacon2 <> "" Then
                    MyTableSoMucKeCon.Rows(j)(2) = Replace(MyTableSoMucKeCon.Rows(j)(2), MyTableSoMucKeCon.Rows(j)(2), "-")
                End If
            Next
        Next

        ' ''''''''''''''''''''Sổ Mục Kê Nội Dung



        Dim MyTableSoMucKeNoiDung As New DataTable
        Dim ctt As New SqlCommand
        Dim dd As New SqlDataAdapter
        Dim g As String = "SELECT * From SoMucKeNoiDung"
        ctt.Connection = conn
        ctt.CommandType = CommandType.Text
        ctt.CommandText = g
        dd.SelectCommand = ctt
        MyTableSoMucKeNoiDung.Clear()
        dd.Fill(MyTableSoMucKeNoiDung)
        Dim bien As New clsInSo

        ''đẩy nội dung từ SoMucKeNoiDung ra bảng MyTableSoMucKe

        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1

            For j = bien.bien To MyTableSoMucKeNoiDung.Rows.Count - 1
                bando1 = MyTableSoMucKe.Rows(i)(1).ToString
                bando2 = MyTableSoMucKeNoiDung.Rows(j)(1).ToString
                noidung1 = MyTableSoMucKe.Rows(i)(14).ToString
                noidung2 = MyTableSoMucKeNoiDung.Rows(j)(3).ToString
                If bando1.Trim = bando2.Trim And noidung2 <> "" Then
                    MyTableSoMucKe.Rows(i)(14) = noidung2.Trim
                    MyTableSoMucKeNoiDung.Rows(j)(3) = ""
                    noidung1 = ""
                    noidung2 = ""
                    bando1 = ""
                    bando2 = ""
                    bien.bien = j + 1
                    Exit For
                ElseIf bando1.Trim = bando2.Trim And noidung2 = "" Then
                    bien.bien = j + 1
                    Exit For
                End If
            Next
        Next

        '''''
        bien.bien = 0
        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1

            For j = bien.bien To MyTableSoMucKeNoiDung.Rows.Count - 1
                bando1 = MyTableSoMucKe.Rows(i)(1).ToString
                bando2 = MyTableSoMucKeNoiDung.Rows(j)(1).ToString
                noidung1 = MyTableSoMucKe.Rows(i)(13).ToString
                noidung2 = MyTableSoMucKeNoiDung.Rows(j)(2).ToString
                If bando1.Trim = bando2.Trim And noidung2 <> "" Then
                    MyTableSoMucKe.Rows(i)(13) = noidung2.Trim
                    MyTableSoMucKeNoiDung.Rows(j)(2) = ""
                    noidung1 = ""
                    noidung2 = ""
                    bando1 = ""
                    bando2 = ""
                    bien.bien = j + 1
                    Exit For
                ElseIf bando1.Trim = bando2.Trim And noidung2 = "" Then
                    bien.bien = j + 1
                    Exit For
                End If
            Next
        Next
        ''''''''''''''''''''''''''''''''''''''''''''''''''''
        ''''''''''''''''''''''''''''''''''''''''''''''''''''
        '''''''''''''''''''''''''''''''''''''''''''''''''''''
        bien.bien = 0
        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1

            For j = bien.bien To MyTableSoMucKeNoiDung.Rows.Count - 1
                bando1 = MyTableSoMucKe.Rows(i)(1).ToString
                bando2 = MyTableSoMucKeNoiDung.Rows(j)(1).ToString
                noidung1 = MyTableSoMucKe.Rows(i)(15).ToString
                noidung2 = MyTableSoMucKeNoiDung.Rows(j)(4).ToString
                If bando1.Trim = bando2.Trim And noidung2 <> "" Then
                    MyTableSoMucKe.Rows(i)(15) = noidung2.Trim
                    MyTableSoMucKeNoiDung.Rows(j)(4) = ""
                    noidung1 = ""
                    noidung2 = ""
                    bando1 = ""
                    bando2 = ""
                    bien.bien = j + 1
                    Exit For
                ElseIf bando1.Trim = bando2.Trim And noidung2 = "" Then
                    bien.bien = j + 1
                    Exit For
                End If
            Next
        Next

        '''''
        bien.bien = 0
        For i As Integer = 0 To MyTableSoMucKe.Rows.Count - 1

            For j = bien.bien To MyTableSoMucKeNoiDung.Rows.Count - 1
                bando1 = MyTableSoMucKe.Rows(i)(1).ToString
                bando2 = MyTableSoMucKeNoiDung.Rows(j)(1).ToString
                noidung1 = MyTableSoMucKe.Rows(i)(16).ToString
                noidung2 = MyTableSoMucKeNoiDung.Rows(j)(5).ToString
                If bando1.Trim = bando2.Trim And noidung2 <> "" Then
                    MyTableSoMucKe.Rows(i)(16) = noidung2.Trim
                    MyTableSoMucKeNoiDung.Rows(j)(5) = ""
                    noidung1 = ""
                    noidung2 = ""
                    bando1 = ""
                    bando2 = ""
                    bien.bien = j + 1
                    Exit For
                ElseIf bando1.Trim = bando2.Trim And noidung2 = "" Then
                    bien.bien = j + 1
                    Exit For
                End If
            Next
        Next

        Dim frm As New somucke
        'Dim frc As New reportchuan
        'frm.Connection = strConnection

        frm.DB = MyTableSoMucKe
        frm.TB = MyTableSoMucKeCon
        ' frm.TT = MyTableSoMucKeNoiDung
        frm.LoadReport()
        frm.Show()



        'Dim cls As New clsInSo
        'cls.Connection = strConnection

        'btnSoCapGCN.Cursor = Cursors.WaitCursor
        'Try

        '    app = CreateObject("Excel.Application")
        'Catch ex As Exception
        '    'If app Is Nothing Then
        '    MsgBox("Hãy cài đặt Excell", MsgBoxStyle.Critical, "DMCLand")
        '    'End If
        '    Exit Sub
        'End Try

        'Try
        '    If MsgBox("Bạn có muốn lưu file?", MsgBoxStyle.YesNo, "Sổ mục kê") = MsgBoxResult.Yes Then

        '        SfDialog.Filter = "Excel 2007 Workbook (*.xlsx)|*.xlsx|Excel 97-2003 Workbook (*.xls)|*.xls"
        '        SfDialog.FileName = "SoMucKe" & cls.DuongDanFile()
        '        SfDialog.ShowDialog()
        '        If SfDialog.FileName <> "" Then

        '            cls.TangI = 0
        '            cls.iDem = 0

        '            Dim MyTable As New DataTable
        '            Dim paras() As String = {"@Flag", "@NamIn", "@MaDVHC"}
        '            Dim Values() As String = {1, GetNam(), CtrChonDonViHanhChinh1.Current_DVHC}

        '            MyTable = cls.GetData_SP("sp_InSoMucKe", Values, paras)
        '            'Mydata = MyTable

        '            If MyTable.Rows.Count <> 0 Then
        '                cls.Config(app, "a3", "ngang")
        '                cls.TieuDeSoMucKe(app)
        '                cls.DonViHanhChinh = CtrChonDonViHanhChinh1.Current_DVHC
        '                cls.ExportSoMucKe(app, MyTable, GetNam(), DonViHanhChinh)
        '                cls.LuuFile(app, SfDialog.FileName)
        '            Else
        '                app = Nothing
        '                MsgBox("Chưa có hồ sơ nào được hoàn thành!", MsgBoxStyle.OkOnly, "DMCLand")

        '            End If
        '        End If
        '    End If
        '    app = Nothing
        'Catch ex As Exception

        '    app = Nothing
        '    MsgBox("    Có lỗi xảy ra!" & ex.Message & vbCrLf & "   Hãy thử lại", MsgBoxStyle.Critical, "DMCLand")
        'End Try
        'cls.ds.Dispose()
        'cls.da.Dispose()

        'btnSoCapGCN.Cursor = Cursors.Default
    End Sub
    Public Function DonViHanhChinh() As String()
        Dim cls As New clsInSo
        cls.Connection = strConnection
        cls.DonViHanhChinh = CtrChonDonViHanhChinh1.Current_DVHC
        Dim dtDVHC As New DataTable
        dtDVHC = cls.SelectDVHC
        Dim arrMangGiaTriDVHC(3) As String
        If dtDVHC.Rows.Count > 0 Then
            arrMangGiaTriDVHC(0) = dtDVHC.Rows(0).Item("TenTinh").ToString.ToUpper
            arrMangGiaTriDVHC(1) = dtDVHC.Rows(0).Item("TenHuyen").ToString.ToUpper
            arrMangGiaTriDVHC(2) = dtDVHC.Rows(0).Item("Ten").ToString.ToUpper
        End If
        Return arrMangGiaTriDVHC
    End Function
    Private Sub btnSoTheoDoiBDDD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSoTheoDoiBDDD.Click

        Dim cls As New clsInSo
        cls.Connection = strConnection
        If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
            MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ cấp GCN", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If

        Dim MytableSoBienDong As New DataTable
        Dim parasSoBienDong() As String = {"@MaDVHC", "@Nam"}
        Dim ValuesSoBienDong(1) As String

        ValuesSoBienDong(0) = CtrChonDonViHanhChinh1.Current_DVHC
        ValuesSoBienDong(1) = GetNam()




        MytableSoBienDong = cls.GetData_SP("SP_SoTheoDoiBienDong", ValuesSoBienDong, parasSoBienDong)

        If MytableSoBienDong.Rows.Count <> 0 Then

            MytableSoBienDong.Columns.Add("Tinh")
            MytableSoBienDong.Columns.Add("Huyen")
            MytableSoBienDong.Columns.Add("Xa")
            MytableSoBienDong.Columns.Add("MaTinh")
            MytableSoBienDong.Columns.Add("MaHuyen")
            MytableSoBienDong.Columns.Add("MaXa")
            MytableSoBienDong.Rows(0).Item("Tinh") = DonViHanhChinh(0)
            MytableSoBienDong.Rows(0).Item("Huyen") = DonViHanhChinh(1)
            MytableSoBienDong.Rows(0).Item("Xa") = DonViHanhChinh(2)



            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim dt As New DataTable
            Dim s As String = "SELECT MaTinh,Mahuyen,MaXa FROM tblTuDienDonViHanhChinh where MaDonViHanhChinh=" & CtrChonDonViHanhChinh1.Current_DVHC.ToString
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            cmd.CommandText = s
            da.SelectCommand = cmd
            dt.Clear()
            da.Fill(dt)
            MytableSoBienDong.Rows(0).Item("MaTinh") = dt.Rows(0)(0)
            MytableSoBienDong.Rows(0).Item("MaHuyen") = dt.Rows(0)(1)
            MytableSoBienDong.Rows(0).Item("MaXa") = dt.Rows(0)(2)

        End If

        Dim frm As New frmSoBienDong
        frm.DB = MytableSoBienDong
        frm.LoadReport()
        frm.Show()




        'Try
        '    app = CreateObject("Excel.Application")
        'Catch ex As Exception
        '    MsgBox("Hãy cài đặt Excel ", MsgBoxStyle.Critical, "DMCLand")
        '    Exit Sub
        'End Try
        'Try
        '    If MsgBox("Bạn có muốn lưu file này không ? ", MsgBoxStyle.YesNo, "Sổ theo dõi biến động") = MsgBoxResult.Yes Then
        '        SfDialog.Filter = "Excel 2007 Workbook (*.xlsx) |*.xlsx|Excel 97-2003 Workbook (*.xls)|*.xls"
        '        SfDialog.FileName = "SoTheoDoiBienDong" & cls.DuongDanFile()
        '        SfDialog.ShowDialog()
        '        If SfDialog.FileName <> "" Then
        '            Dim nam() As Long
        '            cls.Config(app, "a3", "dung")
        '            cls.TrangMoDauSoTheoDoiBienDong(app, DonViHanhChinh)
        '            Dim s As String = "SELECT DISTINCT ThoiDiemDangKy FROM tblDangKyBienDong where ThoiDiemDangKy is not null ORDER BY ThoiDiemDangKy"
        '            cmd.Connection = conn
        '            cmd.CommandType = CommandType.Text
        '            cmd.CommandText = s
        '            da.SelectCommand = cmd
        '            ds.Clear()
        '            ds.Tables.Clear()
        '            da.Fill(ds, "SoTheoDoiBDDD")

        '            Dim dem As Integer = 0
        '            ReDim Preserve nam(dem)
        '            nam(dem) = ds.Tables("SoTheoDoiBDDD").Rows(0).Item(0).year().ToString()

        '            For i = 1 To ds.Tables("SoTheoDoiBDDD").Rows.Count - 1
        '                If nam(dem) <> ds.Tables("SoTheoDoiBDDD").Rows(i).Item(0).year() Then
        '                    dem += 1
        '                    ReDim Preserve nam(dem)
        '                    nam(dem) = ds.Tables("SoTheoDoiBDDD").Rows(i).Item(0).year()

        '                End If

        '            Next

        '            cls.ExportSoTheoDoiBienDong(app, nam, GetNam(), CtrChonDonViHanhChinh1.Current_DVHC)
        '            cls.LuuFile(app, SfDialog.FileName)
        '            app = Nothing
        '        End If
        '    End If
        '    app = Nothing
        'Catch ex As Exception
        '    app = Nothing
        '    MsgBox("    Có lỗi xảy ra!" & vbCrLf & "   Hãy thử lại", MsgBoxStyle.Critical, "DMCLand")
        'End Try
        'ds.Dispose()
        'da.Dispose()
        'btnSoCapGCN.Cursor = Cursors.Default
    End Sub

    Public Function GetNam() As Int16
        If Not chkChonIn.Checked Then
            If cboNam.Text <> "" Then
                strNamIn = cboNam.SelectedItem

            Else
                MessageBox.Show("Chọn năm để in !!!")
                cboNam.Focus()
                Exit Function
            End If
        Else
            strNamIn = 0
        End If
        Return strNamIn
    End Function

    Private Sub chkChonIn_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkChonIn.CheckedChanged
        If chkChonIn.Checked Then
            chkChonIn.Text = "In tất cả"
            cboNam.Enabled = False
            strNamIn = 0
        Else
            chkChonIn.Text = "In theo năm"
            cboNam.Enabled = True
        End If
    End Sub

    Private Sub cmdChonCSD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdChonCSD.Click
        Dim cls As New clsInSo
        cls.Connection = strConnection
        btnSoCapGCN.Cursor = Cursors.WaitCursor
        If (CtrChonDonViHanhChinh1.Current_DVHC = 0) Then
            MessageBox.Show("Hãy chọn đơn vị hành chính để in sổ", "In sổ Mục Kê", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            Exit Sub
        End If
        Dim dt As New DataTable
        Dim parasSoMucKe() As String = {"@MaDVHC", "@Nam", "@MaChu"}
        Dim ValuesSoMucKe() As String = {CtrChonDonViHanhChinh1.Current_DVHC, GetNam(), ""}

        dt = cls.GetData_SP("SP_SoDiaChinh", ValuesSoMucKe, parasSoMucKe)


        Dim frm As New frmNhomChu
        frm.DB = dt
        frm.LoadReport()
        frm.ShowDialog()
        strMaChu = frm.strMaChu
        dt1 = frm.DBByNhomChu
        btnSoDiaChinh.Enabled = True
        'frm.Close()
        'Dim frm As New frmChuSuDung
        ''//Gán chuỗi kết nối cơ sở dữ liệu
        'frm.CtrlChuCQNN1.Connection = strConnection
        'frm.CtrlChuCQNN1.MaHoSoCapGCN = ""
        'frm.CtrlChuCQNN1.TenPhuong = ""
        ''//Thiết lập chế độ active/unactive các chức năng
        'frm.CtrlChuCQNN1.TrangThaiChucNang(False, False)
        'frm.CtrlChuCQNN1.ShowData()

        ''//Gán chuỗi kết nối cơ sở dữ liệu
        'frm.CtrlChuGDCN1.Connection = strConnection
        'frm.CtrlChuGDCN1.MaHoSoCapGCN = ""
        'frm.CtrlChuGDCN1.TenPhuong = ""
        ''//Thiết lập chế độ active/unactive các chức năng
        'frm.CtrlChuGDCN1.TrangThaiChucNang(False, False)
        'frm.CtrlChuGDCN1.ShowData()

        ''//Gán chuỗi kết nối cơ sở dữ liệu
        'frm.CtrlChuTCDN1.Connection = strConnection
        'frm.CtrlChuTCDN1.MaHoSoCapGCN = ""
        'frm.CtrlChuTCDN1.TenPhuong = ""
        ''//Thiết lập chế độ active/unactive các chức năng
        'frm.CtrlChuTCDN1.TrangThaiChucNang(False, False)
        'frm.CtrlChuTCDN1.ShowData()
        'frm.ShowDialog()


        'Dim dt As New DataTable()
        'dt.Columns.Clear()
        'grdChuSuDung.Columns.Clear()
        'For i As Integer = 0 To frm.CtrlChuGDCN1.grdvwChu.ColumnCount - 1
        '    dt.Columns.Add(frm.CtrlChuGDCN1.grdvwChu.Columns(i).Name)
        'Next
        'For i As Integer = 0 To frm.CtrlChuGDCN1.grdvwChu.RowCount - 1
        '    If (frm.CtrlChuGDCN1.grdvwChu.Rows(i).Cells("Chon").Value.ToString() = "True") Then
        '        dt.Rows.Add()
        '        For k As Integer = 0 To dt.Columns.Count - 1
        '            dt.Rows(dt.Rows.Count - 1)(k) = frm.CtrlChuGDCN1.grdvwChu.Rows(i).Cells(k).Value
        '        Next
        '    End If
        'Next
        'For i As Integer = 0 To frm.CtrlChuTCDN1.grdvwChu.RowCount - 1
        '    If (frm.CtrlChuTCDN1.grdvwChu.Rows(i).Cells("Chon").Value.ToString() = "True") Then
        '        dt.Rows.Add()
        '        For k As Integer = 0 To dt.Columns.Count - 1
        '            dt.Rows(dt.Rows.Count - 1)(k) = frm.CtrlChuTCDN1.grdvwChu.Rows(i).Cells(k).Value
        '        Next
        '    End If
        'Next
        'For i As Integer = 0 To frm.CtrlChuCQNN1.grdvwChu.RowCount - 1
        '    If (frm.CtrlChuCQNN1.grdvwChu.Rows(i).Cells("Chon").Value.ToString() = "True") Then
        '        dt.Rows.Add()
        '        For k As Integer = 0 To dt.Columns.Count - 1
        '            dt.Rows(dt.Rows.Count - 1)(k) = frm.CtrlChuCQNN1.grdvwChu.Rows(i).Cells(k).Value
        '        Next
        '    End If
        'Next
        'grdChuSuDung.DataSource = dt
        'FormatGridContruction(grdChuSuDung)
    End Sub

    Private Sub FormatGridContruction(ByVal grdvw As DataGridView)

        Try
            '//Ẩn tất cả các cột
            Me.HideAllColumns(grdChuSuDung)
            '//Chỉ hiển thị những Cột cần thiết

            '//  'Kiểm tra xem nếu có cột chọn thì Hiển thị lên
            If (grdvw.Columns.Contains("Chon")) Then

                grdChuSuDung.Columns("MaChu").HeaderText = "Mã chủ"
                grdChuSuDung.Columns("MaChu").Width = 100
                grdChuSuDung.Columns("MaChu").Visible = False
                grdChuSuDung.Columns("MaChu").DisplayIndex = 0
                grdChuSuDung.Columns("MaChu").SortMode = DataGridViewColumnSortMode.NotSortable

            End If
            '  // 'Họ tên
            If (grdChuSuDung.Columns.Contains("HoTen")) Then

                grdChuSuDung.Columns("HoTen").HeaderText = "Tên"
                grdChuSuDung.Columns("HoTen").Width = 200
                grdChuSuDung.Columns("HoTen").Visible = True
                grdChuSuDung.Columns("HoTen").SortMode = DataGridViewColumnSortMode.NotSortable
            End If

            If (grdChuSuDung.Columns.Contains("DiaChi")) Then

                grdChuSuDung.Columns("DiaChi").HeaderText = "Địa chỉ"
                grdChuSuDung.Columns("DiaChi").Width = 200
                grdChuSuDung.Columns("DiaChi").Visible = True
                grdChuSuDung.Columns("DiaChi").SortMode = DataGridViewColumnSortMode.NotSortable
            End If
            If (grdChuSuDung.Columns.Contains("DiaChi")) Then

                grdChuSuDung.Columns("SoDinhDanh").HeaderText = "Số định danh (CMT ,Số đăng ký)"
                grdChuSuDung.Columns("SoDinhDanh").Width = 300
                grdChuSuDung.Columns("SoDinhDanh").Visible = True
                grdChuSuDung.Columns("SoDinhDanh").SortMode = DataGridViewColumnSortMode.NotSortable
            End If
            If (grdChuSuDung.Columns.Contains("DiaChi")) Then

                '    //'Ngày cấp định danh 1
                grdChuSuDung.Columns("NgayCap").HeaderText = "Ngày cấp"
                grdChuSuDung.Columns("NgayCap").Width = 150
                grdChuSuDung.Columns("NgayCap").Visible = True
                grdChuSuDung.Columns("NgayCap").SortMode = DataGridViewColumnSortMode.NotSortable
            End If
            If (grdChuSuDung.Columns.Contains("DiaChi")) Then

                '         //'Nơi cấp định danh 1
                grdChuSuDung.Columns("NoiCap").HeaderText = "Nơi cấp"
                grdChuSuDung.Columns("NoiCap").Width = 150
                grdChuSuDung.Columns("NoiCap").Visible = True
                grdChuSuDung.Columns("NoiCap").SortMode = DataGridViewColumnSortMode.NotSortable


            End If


            grdChuSuDung.AllowUserToAddRows = False
            grdChuSuDung.AllowUserToDeleteRows = False
            grdChuSuDung.GridColor = Color.WhiteSmoke
            grdChuSuDung.BorderStyle = BorderStyle.Fixed3D
            grdChuSuDung.RowHeadersVisible = False
            grdChuSuDung.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            grdChuSuDung.SelectionMode = DataGridViewSelectionMode.FullRowSelect


        Catch ex As Exception
            MessageBox.Show(" Danh sách Chủ tìm được", "DMCLand", MessageBoxButtons.OK)
        End Try
    End Sub

    Private Sub HideAllColumns(ByVal grdvw As DataGridView)
        '  //'Ẩn tất cả các cột trên Grid

        For i As Integer = 0 To grdvw.Columns.Count - 1
            grdvw.Columns(i).Visible = False
        Next
        grdvw.RowHeadersVisible = False

    End Sub

    Public Function xmlMaChuSuDung(ByVal grd As DataGridView) As XmlDocument
        Dim doc As New XmlDocument()
        Dim nodeRoot As XmlNode = doc.CreateElement("root")
        doc.AppendChild(nodeRoot)
        Dim nodeChuSuDung As XmlNode
        Dim nameNode As XmlNode
        For i As Integer = 0 To grd.RowCount
            '// Tạo một phần tử lồng bên trong (cùng với một đặc tính).
            nodeChuSuDung = doc.CreateElement("MaChus")
            nodeRoot.AppendChild(nodeChuSuDung)
            nameNode = doc.CreateElement("MaChu")
            nameNode.AppendChild(doc.CreateTextNode(grd.Rows(i).Cells("machu").Value.ToString()))
            nodeChuSuDung.AppendChild(nameNode)
        Next
        Return doc
    End Function

    Private Sub RadInSoDiaChinhByCSD_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadInSoDiaChinhByCSD.CheckedChanged
        CheckInSoDiaChinh()
    End Sub

    Private Sub RadInSoDiaChinhAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadInSoDiaChinhAll.CheckedChanged
        CheckInSoDiaChinh()
    End Sub
    Public Sub CheckInSoDiaChinh()
        If RadInSoDiaChinhAll.Checked Then
            cmdChonCSD.Enabled = False
            grdChuSuDung.Enabled = False
            btnSoDiaChinh.Enabled = True
        Else
            cmdChonCSD.Enabled = True
            btnSoDiaChinh.Enabled = False
            grdChuSuDung.Enabled = True
        End If
    End Sub
End Class
